--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_met_mensurlt ;
CREATE TABLE se_met_mensurlt AS (SELECT * FROM &erp.MET_MENSURLT WHERE 1=2);

/*
select * from (
SELECT &outils.stragg(source) source,gene_cod, gene_ind, longpat_cod, taillepat_cod, enreg_id, taillemarq_cod, rang
FROM (
  SELECT 'uv' source ,g.* FROM se_met_mensurlt g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.MET_MENSURLT g
  )
GROUP BY gene_cod, gene_ind, longpat_cod, taillepat_cod, enreg_id, taillemarq_cod, rang
HAVING Count(1)!=2) r
ORDER BY gene_cod, gene_ind, longpat_cod,taillepat_cod*/



 INSERT INTO se_met_mensurlt
 (SELECT SubStr(p.prodbase,1,Length(p.prodbase)-1) gene_cod,
         p.indice gene_ind,
         Nvl(lpa,'vide') longpat_cod,
         tpa.tpa taillepat_cod,
         0 enreg_id,
         tma.tma taillemarq_cod,
         Rank() OVER (PARTITION BY m.cleprod,lpa,lma ORDER BY m.cleprod,lpa,lma,tpa,tma)  rang
    FROM  UV_PMENSURATIONS m,uv_pproduits p,uv_pproduits_statut_actuel s
      ,UV_PMENSURATIONS_TMA tma,UV_PMENSURATIONS_TPA tpa
    WHERE p.id=m.cleprod
      AND tma.id=m.id AND tpa.id=tma.id AND tpa.rangmulti2 = tma.rangmulti2       -- AND p.prodbase='P611323510440'
      AND s.id=p.id and (s.statut<900 OR statut IS NULL) ) ;

  --SELECT * FROM UV_PPTMENSURATIONS WHERE id LIKE '%P61132351044%'
  --SELECT * FROM UV_PMENSURATIONS WHERE id LIKE '%P61132351044%'

  /*supprime les .. inutile avant les valeur entre "" */
  UPDATE se_met_mensurlt SET longpat_cod=REGEXP_REPLACE(longpat_cod,'^(\.{1,})(.{1,})','\2');
  UPDATE se_met_mensurlt SET taillepat_cod=REGEXP_REPLACE(taillepat_cod,'^(\.{1,})(.{1,})','\2');
  UPDATE se_met_mensurlt SET taillemarq_cod=REGEXP_REPLACE(taillemarq_cod,'^(\.{1,})(.{1,})','\2');

  /*supprime les .. inutile apr�s les valeur entre "" */
  UPDATE se_met_mensurlt SET longpat_cod=REGEXP_REPLACE(longpat_cod,'(.{1,})(\.{1,})$','\1');
  UPDATE se_met_mensurlt SET taillepat_cod=REGEXP_REPLACE(taillepat_cod,'(.{1,})(\.{1,})$','\1');
  UPDATE se_met_mensurlt SET taillemarq_cod=REGEXP_REPLACE(taillemarq_cod,'(.{1,})(\.{1,})$','\1');

  UPDATE se_met_mensurlt SET longpat_cod='STD' WHERE longpat_cod='.' ;
  UPDATE se_met_mensurlt SET taillepat_cod='STD' WHERE taillepat_cod='.' ;
  UPDATE se_met_mensurlt SET taillemarq_cod='STD' WHERE taillemarq_cod='.' ;


 UPDATE se_met_mensurlt tf SET enreg_id=(select enreg_id FROM &erp.MET_MENSURlt f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind AND f.longpat_cod=tf.longpat_cod AND f.taillepat_cod=tf.taillepat_cod );
 UPDATE se_met_mensurlt tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL;



 CALL uvse_synchroverserp('gene_cod@gene_ind@longpat_cod@taillepat_cod','MET_MENSURLT','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,gene_cod, gene_ind, longpat_cod, taillepat_cod, enreg_id, taillemarq_cod, rang
FROM (
  SELECT 'uv' source ,g.* FROM se_met_mensurlt g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.MET_MENSURLT g
  )
GROUP BY gene_cod, gene_ind, longpat_cod, taillepat_cod, enreg_id, taillemarq_cod, rang
HAVING Count(1)!=2) r
ORDER BY gene_cod, gene_ind, longpat_cod,taillepat_cod ;